﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Collections;
using Com.Crc.PoJo.Model.Resource;
using SnailGame.Utility.DataAccess;

namespace Com.Crc.ResManage.DAL.EquipManage
{
    public class BroadBandManageDAL
    {
        DataBroker broker = new DataBroker();

        /// <summary>
        /// 新增宽带设备信息
        /// </summary>
        /// <param name="data">model实体</param>
        /// <returns></returns>
        public bool AddWLANData(WLANEntity data)
        {
            string strSql = @" INSERT INTO TT_RESOURCE_WLAN(N_SUBSTATION_ID,N_DEP_BRANCH_ID,N_UNITTYPE_ID,S_NAME,S_ADDRESS,N_VLAN,N_QnQVLAN,N_BAS_ID,
                                           N_BAS_PORT,N_UP_LEVEL,N_M_UP_ID,N_M_UP_PORT,S_M_NAME,S_BAK,S_STATE,D_TIME)
                                    VALUES (" + data.N_SUBSTATION_ID + "," + data.N_DEP_BRANCH_ID + "," + data.N_UNITTYPE_ID + ",'" + data.S_NAME + "','" 
                                           + data.S_ADDRESS + "'," + data.N_VLAN + "," + data.N_QnQVLAN + "," + data.N_BAS_ID + ","
                                           + data.N_BAS_PORT + "," + data.N_UP_LEVEL + "," + data.N_M_UP_ID + "," + data.N_M_UP_PORT + ",'" + data.S_M_NAME + "','" 
                                           + data.S_BAK + "','" + data.S_STATE + "','" + data.D_TIME.ToString("yyyy-MM-dd HH:mm:ss") + "')";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 删除设备，修改状态位
        /// </summary>
        /// <param name="wlanList"></param>
        /// <returns></returns>
        public bool DeleteWLANData(ArrayList wlanList)
        {
            string strWlanID = "";
            for (int i = 0; i < wlanList.Count; i++)
            {
                strWlanID += wlanList[i] + ",";
            }
            strWlanID = strWlanID.Substring(0, strWlanID.Length - 1);
            string strSql = @"UPDATE TT_RESOURCE_WLAN SET S_STATE='0' WHERE N_ID IN (" + strWlanID + ")";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 修改宽带设备信息
        /// </summary>
        /// <param name="data">model实体</param>
        /// <returns></returns>
        public bool UpdateWLANData(WLANEntity data)
        {
            string strSql = @" UPDATE TT_RESOURCE_WLAN
                                  SET N_SUBSTATION_ID = " + data.N_SUBSTATION_ID + @", 
                                      N_DEP_BRANCH_ID = " + data.N_DEP_BRANCH_ID + @", 
                                      N_UNITTYPE_ID = " + data.N_UNITTYPE_ID + @",
                                      S_NAME = '" + data.S_NAME + @"', 
                                      S_ADDRESS = '" + data.S_ADDRESS+ @"',
                                      N_VLAN = " + data.N_VLAN + @", 
                                      N_QnQVLAN = " + data.N_QnQVLAN+ @",
                                      N_BAS_ID = " + data.N_BAS_ID + @", 
                                      N_BAS_PORT = " + data.N_BAS_PORT+ @",
                                      N_UP_LEVEL = " + data.N_UP_LEVEL + @", 
                                      N_M_UP_ID = " + data.N_M_UP_ID+ @",
                                      N_M_UP_PORT = " + data.N_M_UP_PORT + @",
                                      S_M_NAME = '" + data.S_M_NAME + @"', 
                                      S_BAK = '" + data.S_BAK+ @"', 
                                      D_TIME = '" + data.D_TIME.ToString("yyyy-MM-dd HH:mm:ss") + @"'
                                WHERE N_ID=" + data.N_ID;
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 查询宽带设备信息
        /// </summary>
        /// <param name="strBranchID">分局ID</param>
        /// <param name="strOfficeID">局点ID</param>
        /// <param name="strEquipID">设备ID</param>
        /// <returns></returns>
        public DataTable QueryWLANData(string strBranchID, string strOfficeID, string strEquipID)
        {
            string strSql = @" SELECT a.N_ID,b.S_NO,a.N_SUBSTATION_ID,a.N_DEP_BRANCH_ID,a.N_UNITTYPE_ID,a.S_NAME,a.S_ADDRESS,
                                      a.N_VLAN,a.N_QnQVLAN,a.N_BAS_ID,a.N_BAS_PORT,a.N_UP_LEVEL,a.N_M_UP_ID,a.N_M_UP_PORT,a.S_M_NAME,
                                      a.S_BAK,a.S_STATE,a.D_TIME 
                                 FROM TT_RESOURCE_WLAN a
                           INNER JOIN TT_RESOURCE_SUBSTATION b ON a.N_SUBSTATION_ID = b.N_ID
                                WHERE a.S_STATE='1' ";
            if(strBranchID != "")
            {
                strSql += " AND a.N_DEP_BRANCH_ID = " + strBranchID;
            }
            if (strOfficeID != "")
            {
                strSql += " AND a.N_SUBSTATION_ID = " + strOfficeID;
            }
            if (strEquipID != "")
            {
                strSql += " AND a.N_ID = " + strEquipID;
            }
            return broker.ExecuteQueryForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }
    }
}
